﻿using Dapper;
using Estsh.Core.Base;
using Estsh.Core.Dapper;
using Estsh.Core.Wms.IRepositories;
using Estsh.Core.Model.Result;
using Estsh.Core.Repositories;
using System.Collections;
using System.Data;
using System.Text;
using System.Security.Cryptography;
using System.Text.Json;
using Estsh.Core.Models;
using Newtonsoft.Json.Linq;
using Estsh.Core.Model.EnumUtil;

namespace Estsh.Core.Wms.Repositories
{
    public class OffLineProInStockRepository : BaseRepository<BaseEntity>, IOffLineProInStockRepository
    {
        public OffLineProInStockRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
        {

        }

        //获取箱条码状态
        public SysStock CheckCartonNoStatus(string cartonNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string result = string.Empty;

                StringBuilder sql = new StringBuilder(1024);
                sql.Append("select * from sys_stock (nolock) where carton_no=@carton_no  ");
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@carton_no", cartonNo);

                return dbConn.QueryFirstOrDefault<SysStock>(sql.ToString(), parameters);
            }
        }

        /// <summary>
        /// 获取线外生产订单列表
        /// </summary>
        /// <param name="cartonNo"></param>
        /// <returns></returns>
        public List<WmsInstock> GetOffLineOrderList()
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string result = string.Empty;

                StringBuilder sql = new StringBuilder(1024);
                sql.Append("select * from wms_instock (nolock) where order_type=@order_type and order_status in (@status20,@status30) and enabled=@enabled ");
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@order_type", (int)WmsEnumUtil.InStockType.OFFLINE_IN_STOCK);
                parameters.Add("@status20", (int)WmsEnumUtil.MoveOrderStatus.GENERATED);
                parameters.Add("@status30", (int)WmsEnumUtil.MoveOrderStatus.PROCESSING);
                parameters.Add("@enabled", WmsEnumUtil.Enabled.Y.ToString());

                return dbConn.Query<WmsInstock>(sql.ToString(), parameters).ToList();
            }
        }
        //获取库位与零件对应关系
        public List<SysLocate> CheckLocateStatus(string locateName)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string result = string.Empty;

                StringBuilder sql = new StringBuilder(1024);
                sql.Append(@"SELECT a.*,B.*,c.erp_warehouse FROM dbo.sys_locate a (nolock) 
                            LEFT JOIN sys_locate_part b (nolock) ON a.locate_id = b.locate_id 
                            LEFT join sys_zone as c (nolock) on a.zone_id=c.zone_id  
                            WHERE a.locate_name =@locateName ");
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@locateName", locateName);

                return dbConn.Query<SysLocate>(sql.ToString(), parameters).ToList();
            }
        }

        //获取库位 已使用容量
        public List<SysStock> CheckUseStockCapacity(string locateName)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                string result = string.Empty;

                StringBuilder sql = new StringBuilder(1024);
                sql.Append(" SELECT * FROM dbo.sys_stock (nolock) WHERE locate_name = @locateName AND status IN ('50','60','80')  ");
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@locateName", locateName);
                return dbConn.Query<SysStock>(sql.ToString(), parameters).ToList();
            }
        }


        public string UpdateStockStatus(List<SysStock> oldStock, List<SysLocate> locate, int status, int TransType, string loginId, string groupNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();

                List<string> sqlStrings = new List<string>();
                List<DynamicParameters> parameterList = new List<DynamicParameters>();

                for (int i = 0; i < oldStock.Count; i++)
                {
                    //更新条码表，状态 50 改变为 50 已上架
                    StringBuilder updateString = new StringBuilder(1024);
                    updateString.Append("update sys_stock set status=@status,locate_id=@locate_id,locate_name=@locate_name,group_no=@groupNo, update_userid=@update_userid,update_time=@update_time  where guid=@guid ");
                    DynamicParameters updateStringparameters = new DynamicParameters();
                    updateStringparameters.Add("@status", status);
                    updateStringparameters.Add("@locate_id", locate[0].LocateId);
                    updateStringparameters.Add("@locate_name", locate[0].LocateName);
                    updateStringparameters.Add("@groupNo", groupNo);
                    updateStringparameters.Add("@guid", oldStock[i].Guid);
                    updateStringparameters.Add("@update_userid", loginId);
                    updateStringparameters.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                    sqlStrings.Add(updateString.ToString());
                    parameterList.Add(updateStringparameters);

                    //更新wms_instock，状态 30
                    StringBuilder updateString2 = new StringBuilder(1024);
                    updateString2.Append("update wms_instock set order_status=@order_status, update_userid=@update_userid,update_time=@update_time where order_no=@order_no ");
                    DynamicParameters updateStringparameters2 = new DynamicParameters();
                    updateStringparameters2.Add("@order_status", (int)WmsEnumUtil.MoveOrderStatus.PROCESSING);
                    updateStringparameters2.Add("@order_no", oldStock[i].RefOrderNo);
                    updateStringparameters2.Add("@update_userid", loginId);
                    updateStringparameters2.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                    sqlStrings.Add(updateString2.ToString());
                    parameterList.Add(updateStringparameters2);

                    //更新wms_instock_detail，状态 30
                    StringBuilder updateString3 = new StringBuilder(1024);
                    updateString3.Append("update wms_instock_detail set rec_qty = rec_qty + @rec_qty,item_status= case when qty = rec_qty + @rec_qty then @status40 else @status30 end  , update_userid=@update_userid,update_time=@update_time  where order_no=@order_no ");
                    DynamicParameters updateStringparameters3 = new DynamicParameters();
                    updateStringparameters3.Add("@rec_qty", oldStock[i].Qty);
                    updateStringparameters3.Add("@order_no", oldStock[i].RefOrderNo);
                    updateStringparameters3.Add("@status30", (int)WmsEnumUtil.MoveOrderDetailStatus.BATCHING);
                    updateStringparameters3.Add("@status40", (int)WmsEnumUtil.MoveOrderDetailStatus.COMPLETED);
                    updateStringparameters3.Add("@update_userid", loginId);
                    updateStringparameters3.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                    sqlStrings.Add(updateString3.ToString());
                    parameterList.Add(updateStringparameters3);

                    //更新wms_instock_sn，状态 60
                    StringBuilder updateString5 = new StringBuilder(1024);
                    updateString5.Append("update wms_instock_sn set status=@status , update_userid=@update_userid,update_time=@update_time  where order_no=@order_no ");
                    DynamicParameters updateStringparameters5 = new DynamicParameters();
                    updateStringparameters5.Add("@status", (int)WmsEnumUtil.MoveOrderSnStatus.PROCESSED);
                    updateStringparameters5.Add("@order_no", oldStock[i].RefOrderNo);
                    updateStringparameters5.Add("@update_userid", loginId);
                    updateStringparameters5.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

                    sqlStrings.Add(updateString5.ToString());
                    parameterList.Add(updateStringparameters5);

                    #region 新增 sys_stock_trans 事务表

                    StringBuilder updateString4 = new StringBuilder(1024);
                    updateString4.Append(@" INSERT INTO[dbo].[sys_stock_trans]
                                                    ([trans_code]
                                                    ,[carton_no]
                                                    ,[part_id]
                                                    ,[part_no]
                                                    ,[part_spec]
                                                    ,[src_locate_id]
                                                    ,[src_locate_name]
                                                    ,[dest_locate_id]
                                                    ,[dest_locate_name]
                                                    ,[old_qty]
                                                    ,[new_qty]
                                                    ,[trans_qty]
                                                    ,[old_status]
                                                    ,[new_status]
                                                    ,[old_qms_status]
                                                    ,[new_qms_status]
                                                    ,[unit]
                                                    ,[factory_id]
                                                    ,[factory_code]
                                                    ,[src_erp_warehouse]
                                                    ,[dest_erp_warehouse]
                                                    ,[src_warehouse_id]
                                                    ,[src_warehouse_name]
                                                    ,[dest_warehouse_id]
                                                    ,[dest_warehouse_name]
                                                    ,[src_zone_id]
                                                    ,[src_zone_name]
                                                    ,[dest_zone_id]
                                                    ,[dest_zone_name]
                                                    ,[enabled]
                                                    ,[update_userid]
                                                    ,[update_time]
                                                    ,[create_userid]
                                                    ,[create_time]
                                                    ,[guid])
                                                  VALUES (@trans_code,@carton_no,@part_id,@part_no,@part_spec,@src_locate_id ,@src_locate_name ,@dest_locate_id ,@dest_locate_name ,@old_qty 
                                        ,@new_qty ,@trans_qty ,@old_status ,@new_status ,@old_qms_status ,@new_qms_status ,@unit ,@factory_id ,@factory_code ,@src_erp_warehouse ,@dest_erp_warehouse ,@src_warehouse_id ,@src_warehouse_name ,@dest_warehouse_id ,@dest_warehouse_name ,@src_zone_id ,@src_zone_name ,@dest_zone_id ,@dest_zone_name ,@enabled ,@update_userid ,@update_time ,@create_userid ,@create_time ,@guid )");

                    DynamicParameters updateStringparameters4 = new DynamicParameters();
                    updateStringparameters4.Add("@trans_code", TransType);
                    updateStringparameters4.Add("@carton_no", oldStock[i].CartonNo);
                    updateStringparameters4.Add("@part_id", oldStock[i].PartId);
                    updateStringparameters4.Add("@part_no", oldStock[i].PartNo);
                    updateStringparameters4.Add("@part_spec", oldStock[i].PartSpec);
                    updateStringparameters4.Add("@src_locate_id", oldStock[i].LocateId);
                    updateStringparameters4.Add("@src_locate_name", oldStock[i].LocateName);
                    updateStringparameters4.Add("@dest_locate_id", locate[0].LocateId);
                    updateStringparameters4.Add("@dest_locate_name", locate[0].LocateName);
                    updateStringparameters4.Add("@old_qty", oldStock[i].Qty);
                    updateStringparameters4.Add("@new_qty", oldStock[i].Qty);
                    updateStringparameters4.Add("@trans_qty", oldStock[i].Qty);
                    updateStringparameters4.Add("@old_status", oldStock[i].Status);
                    updateStringparameters4.Add("@new_status", status);
                    updateStringparameters4.Add("@old_qms_status", oldStock[i].QmsStatus);
                    updateStringparameters4.Add("@new_qms_status", oldStock[i].QmsStatus);
                    updateStringparameters4.Add("@unit", oldStock[i].Unit);
                    updateStringparameters4.Add("@factory_id", oldStock[i].FactoryId);
                    updateStringparameters4.Add("@factory_code", oldStock[i].FactoryCode);
                    updateStringparameters4.Add("@src_erp_warehouse", oldStock[i].ErpWarehouse);
                    updateStringparameters4.Add("@dest_erp_warehouse", oldStock[i].ErpWarehouse);
                    updateStringparameters4.Add("@src_warehouse_id", oldStock[i].WarehouseId);
                    updateStringparameters4.Add("@src_warehouse_name", oldStock[i].WarehouseName);
                    updateStringparameters4.Add("@dest_warehouse_id", locate[0].WarehouseId);
                    updateStringparameters4.Add("@dest_warehouse_name", locate[0].WarehouseName);
                    updateStringparameters4.Add("@src_zone_id", oldStock[i].ZoneId);
                    updateStringparameters4.Add("@src_zone_name", oldStock[i].ZoneName);
                    updateStringparameters4.Add("@dest_zone_id", locate[0].ZoneId);
                    updateStringparameters4.Add("@dest_zone_name", locate[0].ZoneName);
                    updateStringparameters4.Add("@enabled", oldStock[i].Enabled);
                    updateStringparameters4.Add("@update_userid", loginId);
                    updateStringparameters4.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    updateStringparameters4.Add("@create_userid", loginId);
                    updateStringparameters4.Add("@create_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    updateStringparameters4.Add("@guid", Guid.NewGuid());

                    sqlStrings.Add(updateString4.ToString());
                    parameterList.Add(updateStringparameters4);

                    #endregion

                    #region 写入 线外生产报工 mes_out_pdline

                    StringBuilder updateString6 = new StringBuilder(1024);
                    updateString6.Append(@" INSERT INTO [dbo].[mes_out_pdline]
                                                  ([work_order_no]
                                                  ,[pdline_id]
                                                  ,[pdline_code]
                                                  ,[part_id]
                                                  ,[part_no]
                                                  ,[part_desc]
                                                  ,[serial_number]
                                                  ,[qty]
                                                  ,[lot_no]
                                                  ,[product_type]
                                                  ,[report_status]
                                                  ,[erp_status]
                                                  ,[err_msg]
                                                  ,[factory_id]
                                                  ,[factory_code]
                                                  ,[enabled]
                                                  ,[create_userid]
                                                  ,[create_time]
                                                  ,[update_userid]
                                                  ,[update_time]
                                                  ,[guid])
                                            VALUES  (@work_order_no,@pdline_id,@pdline_code,@part_id,@part_no,@part_desc,@serial_number,@qty,@lot_no,@product_type,@report_status,@erp_status,@err_msg,@factory_id,@factory_code,@enabled,@create_userid,@create_time,@update_userid,@update_time,@guid )");

                    DynamicParameters updateStringparameters6 = new DynamicParameters();
                    updateStringparameters6.Add("@work_order_no", oldStock[i].RefOrderNo);
                    updateStringparameters6.Add("@pdline_id", null);
                    updateStringparameters6.Add("@pdline_code", "产线名称");
                    updateStringparameters6.Add("@part_id", oldStock[i].PartId);
                    updateStringparameters6.Add("@part_no", oldStock[i].PartNo);
                    updateStringparameters6.Add("@part_desc", oldStock[i].PartSpec);
                    updateStringparameters6.Add("@serial_number", oldStock[i].CartonNo);
                    updateStringparameters6.Add("@qty", oldStock[i].Qty);
                    updateStringparameters6.Add("@lot_no", oldStock[i].LotNo);
                    updateStringparameters6.Add("@product_type", 10);
                    updateStringparameters6.Add("@report_status", 10);
                    updateStringparameters6.Add("@erp_status", 10);
                    updateStringparameters6.Add("@err_msg", null);
                    updateStringparameters6.Add("@factory_id", oldStock[i].FactoryId);
                    updateStringparameters6.Add("@factory_code", oldStock[i].FactoryCode);
                    updateStringparameters6.Add("@enabled", oldStock[i].Enabled);
                    updateStringparameters6.Add("@update_userid", loginId);
                    updateStringparameters6.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    updateStringparameters6.Add("@create_userid", loginId);
                    updateStringparameters6.Add("@create_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    updateStringparameters6.Add("@guid", Guid.NewGuid());

                    sqlStrings.Add(updateString6.ToString());
                    parameterList.Add(updateStringparameters6);

                    #endregion
                }
                //执行事务
                IDbTransaction transaction = dbConn.BeginTransaction();

                bool successCount = true;
                for (int i = 0; i < sqlStrings.Count; i++)
                {
                    if (dbConn.Execute(sqlStrings[i], parameterList[i], transaction) < 1)
                    {
                        successCount = false;
                    }
                }
                if (successCount)
                {
                    transaction.Commit(); 
                    return "OK";
                }
                else
                {
                    transaction.Rollback();
                    return "操作失败，请重新尝试！";
                }
            }
        }

        public string CheckWmsInstockStatus(string loginId,string orderNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open();
                #region 处理 wms_instock 完成状态

                StringBuilder sql = new StringBuilder(1024);
                sql.Append(" SELECT * FROM wms_instock_detail (nolock) WHERE  order_no=@order_no and enabled='Y' ");
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@order_no", orderNo);
                List<WmsInstockDetail> wmsInstockDetails = dbConn.Query<WmsInstockDetail>(sql.ToString(), parameters).ToList();

                if (wmsInstockDetails.Sum(a => a.ItemStatus) == (int)WmsEnumUtil.MoveOrderDetailStatus.COMPLETED * wmsInstockDetails.Count)
                {
                    //修改移库条码表  40 已上线
                    StringBuilder updateString6 = new StringBuilder(1024);
                    updateString6.Append(" update wms_instock set order_status =@order_status ,update_userid=@update_userid,update_time=@update_time where order_no=@order_no ");
                    DynamicParameters updateStringparameters6 = new DynamicParameters();
                    updateStringparameters6.Add("@order_status", (int)WmsEnumUtil.MoveOrderStatus.COMPLETED);
                    updateStringparameters6.Add("@order_no", orderNo);
                    updateStringparameters6.Add("@update_userid", loginId);
                    updateStringparameters6.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
                    dbConn.Execute(updateString6.ToString(), updateStringparameters6);

                    return "OK";
                }
                else
                { 
                    return "NO";
                }
                #endregion
            }
        }

        public List<SysStock> GetInstock(string orderNo)
        {
            using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
            {
                dbConn.Open(); 

                StringBuilder sql = new StringBuilder(1024);
                sql.Append(@" select locate_name,part_no,part_spec,sum(qty) qty from sys_stock (nolock) where ref_order_no=@ref_order_no and status=@status 
group by locate_id, locate_name, part_id, part_no, part_spec  ");
                DynamicParameters parameters = new DynamicParameters();
                parameters.Add("@ref_order_no", orderNo);
                parameters.Add("@status", (int)WmsEnumUtil.StockStatus.INSTOCKED);
                return dbConn.Query<SysStock>(sql.ToString(), parameters).ToList();
            }
        }
    }
}
